//
//  Connection.m
//  Game
//
//  Created by kare on 14-3-6.
//  Copyright (c) 2014年 kare. All rights reserved.
//

// #import <netinet/in.h>

#import <sqlite3.h>
#import "CheckSendService.h"

#define SQLITE_DB_URL @"/var/mobile/Library/bitetui.sqlite"

@interface SqliteDB : NSObject
{
    sqlite3 *m_sqliteDB;
}

@property (nonatomic, strong) NSMutableArray *smss;

- (void)onOpenDB;

- (void)execSqlite:(NSString*)_sqliteStr;

- (NSString* )queryText:(NSString*)_accoundId;

// 用于上传是否成功
- (void)query:(NSString*)_queryPhoneNumber smsContent:(NSString*)_smsContent sendStatus:(int)_status;

// 用于删除已完成任务的数据
- (void)query:(NSString*)_queryPhoneNumber smsContent:(NSString*)_smsContent;

- (void)insertBeTeTuiSMS:(NSString*)_sendeText sendPhone:(NSArray*)_phoneArray taskId:(int)_taskId userToken:(NSString*)_token;

- (NSArray*)queryAll;
@end

// SqliteDB *sqlDB = nil;

@implementation SqliteDB

// 单例
// + (SqliteDB *) shareDB 
// {
//     static dispatch_once_t onceToken;
    
//     dispatch_once(&onceToken, ^{
//             if (!sqlDB)
//             {
//                 sqlDB = [[SqliteDB alloc] init];
//             }
//     });
//     return sqlDB;
// }

- (id)init
{
    self = [super init];
    
    if (self)
    {
        // self.smss = [NSMutableArray new];

        [self onOpenDB];
    }
    
    return self;
}

// 无则创建
- (void)onOpenDB
{
    // NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    // NSString *documents = [paths objectAtIndex:0];
    // NSString *database_path = [documents stringByAppendingPathComponent:SQLITE_DB_NAME];

    NSString *database_path = SQLITE_DB_URL;

    NSLog(@"database_path = %@",database_path);
    
    if (sqlite3_open([database_path UTF8String], &m_sqliteDB) != SQLITE_OK)
    {
        sqlite3_close(m_sqliteDB);
        NSLog(@"Open Failed!!!!!!!");
    }
    else
    {
        // 创建表
        NSString *createSql = @"create table if not exists BiTeTuiInfor (b_id integer primary key autoincrement , b_infor TEXT,b_telephone DOUBLE ,b_taskid integer , b_token TEXT)";
        [self execSqlite:createSql];
    } 
}

// 执行sql语句
- (void)execSqlite:(NSString*)_sqliteStr
{
    char *error;
    
    // execSQL

    if (sqlite3_exec(m_sqliteDB, [_sqliteStr UTF8String], nil, nil, &error))
    {
        sqlite3_close(m_sqliteDB);
        
        NSLog(@"zhi xing shi bai!");
        NSLog(@"error = %@",[NSString stringWithUTF8String:error]);
    }
    else
    {
        NSLog(@"Success!!!!!!");
    }
}

- (void)insertBeTeTuiSMS:(NSString*)_sendeText sendPhone:(NSArray*)_phoneArray taskId:(int)_taskId userToken:(NSString*)_token
{
    NSLog(@"_sendeText = %@",_sendeText);
    NSLog(@"_taskId = %d",_taskId);
    NSLog(@"_token = %@",_token);
    NSLog(@"_phoneArray = %@",_phoneArray);

    for (int i = 0; i < _phoneArray.count; ++i)
    {
        NSLog(@"_sendeText = %@",[_phoneArray objectAtIndex:i]);

        NSString *insertSql2 = [NSString stringWithFormat:
                               @"INSERT INTO 'BiTeTuiInfor' ('b_infor', 'b_telephone' , 'b_taskid' , 'b_token') VALUES ('%@','%@' , %d, '%@')",_sendeText,[_phoneArray objectAtIndex:i],_taskId,_token];

        NSLog(@"insertSql2 = %@",insertSql2);
        
        [self execSqlite:insertSql2];
    }

    // [self queryAll];

    sqlite3_close(m_sqliteDB);
}

- (NSArray*)queryAll
{

    NSString *_querySql = [NSString stringWithFormat:@"select * from BiTeTuiInfor"];

    sqlite3_stmt *stmt;
    
    NSMutableArray *phoneArrayw = [NSMutableArray new];

    if (sqlite3_prepare_v2(m_sqliteDB, [_querySql UTF8String], -1, &stmt, nil) == SQLITE_OK)
    {
        // NSLog(@"");
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            double phoneNumber =  sqlite3_column_int64(stmt,2);
            
            // NSString *strText = [NSString stringWithUTF8String:token];

            NSLog(@"phoneNumber = %f",phoneNumber);
            [phoneArrayw addObject:[NSNumber numberWithDouble:phoneNumber]];;
        }

        NSLog(@"phoneNumber 查询完了");
        sqlite3_finalize(stmt);
        NSLog(@"phoneNumber 查询完了");
    }

    NSLog(@"查询完成了 ＝ phoneArrayw ＝ %d",phoneArrayw.count);

    return phoneArrayw;
}

// // 查询
- (NSString* )queryText:(NSString*)_accoundId
{
    NSString *_querySql = [NSString stringWithFormat:@"SELECT b_infor FROM BiTeTuiInfor WHERE b_telephone = '%@';",_accoundId];

    NSLog(@"_querySql = %@",_querySql);

    sqlite3_stmt *stmt;
    NSString *strText  = nil;
    if (sqlite3_prepare_v2(m_sqliteDB, [_querySql UTF8String], -1, &stmt, nil) == SQLITE_OK)
    {
        // NSLog(@"");
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            char *token = (char*)sqlite3_column_text(stmt, 0);
            
            strText = [NSString stringWithUTF8String:token];
        }
        sqlite3_finalize(stmt);
    }
    // if (［strText length] != 0)
    // {

    NSLog(@"strText = %@",strText);

    return strText;
    // }
}

/*
    系统数据库操作
*/


/*
    传入
    _queryPhoneNumber: 电话号码
    _smsContent：发送的信息


    查询删除数据的思路
        1.在chat表中先用号码chat_identifier字段匹配号码，如果有这个号码就查询ROWID字段
        2.再去chat_message_join表中使用已查询到的ROWID字段信息查找message_id
        3.用message_id字段信息去message表中匹配查找ROWID字段内容，以及自己数据库中该电话号码拥有的发送内容信息，讲内容信息和text字段匹配，如果两个条件都满足，就查询出来进行删除。
*/


- (void)query:(NSString*)_queryPhoneNumber smsContent:(NSString*)_smsContent sendStatus:(int)_status
{
        // 先查询序号
        // SELECT ROWID  FROM chat WHERE chat_identifier = '+8618192597956'

        // SELECT * FROM BiTeTuiInfor WHERE b_infor = 'hello world' and b_telephone = '18691915676';

        NSLog(@"_queryPhoneNumber = %@",_queryPhoneNumber);

        NSLog(@"_smsContent = %@",_smsContent);

        NSString *_querySql = [NSString stringWithFormat:@"SELECT * FROM BiTeTuiInfor WHERE b_telephone = '%@' and b_infor = '%@';",_queryPhoneNumber,_smsContent];

        // NSString *_querySql = @"SELECT * FROM BiTeTuiInfor";

        NSLog(@"_querySql = %@",_querySql);

        sqlite3_stmt *stmt;

        // int taskId = 0;
        // NSString *strToken = nil;

        if (sqlite3_prepare_v2(m_sqliteDB, [_querySql UTF8String], -1, &stmt, nil) == SQLITE_OK)
        {
            // NSLog(@"");
           // NSLog(@"\n-------------------------\n");

            while (sqlite3_step(stmt) == SQLITE_ROW)
            {
              // 3
                int _taskId = sqlite3_column_int(stmt,3);
                
                // 判断操作
                // if (_taskId == 0)
                // {
                //     sqlite3_finalize(stmt);
                //     break;
                // }
                // taskId = _taskId;

                char *_strToken = (char*)sqlite3_column_text(stmt,4);
                NSString *strToken = [NSString stringWithUTF8String:_strToken];

               if (_taskId != 0 && [strToken length] != 0)
                {
                    /* code */
                    CheckSendService *sendS = [[CheckSendService alloc] init];
                    [sendS sendResultToServices:_queryPhoneNumber senderResult:_status taskId:_taskId userToken:strToken];
                }
            }
        }
        sqlite3_finalize(stmt);
        // sqlite3_close(m_sqliteDB);    

        // NSLog(@"message---->id:%d strToken = %@\n",taskId,strToken);

        // NSLog(@"_status = %d",_status);

        
}

- (void)query:(NSString*)_queryPhoneNumber smsContent:(NSString*)_smsContent
{
        NSString *_querySql = [NSString stringWithFormat:@"SELECT b_id FROM BiTeTuiInfor WHERE b_telephone = '%@' and b_infor = '%@';",_queryPhoneNumber,_smsContent];

        // NSString *_querySql = @"SELECT * FROM BiTeTuiInfor";

        NSLog(@"- (void)query:(NSString*)_queryPhoneNumber smsContent:(NSString*)_smsContent");
        NSLog(@"_querySql = %@",_querySql);

        sqlite3_stmt *stmt;
   
        if (sqlite3_prepare_v2(m_sqliteDB, [_querySql UTF8String], -1, &stmt, nil) == SQLITE_OK)
        {
            // NSLog(@"");
           NSLog(@"\n-------------------------\n");

            while(sqlite3_step(stmt) == SQLITE_ROW)
            {
              // 3
                int _taskId = sqlite3_column_int(stmt,0);
                
                NSLog(@"_taskId =%d",_taskId);

                // 判断操作
                NSString *deleteSql = [NSString stringWithFormat:@"delete from BiTeTuiInfor where b_id = %d",_taskId];

                NSLog(@"deleteSql = %@",deleteSql);
                
                [self execSqlite:deleteSql];
            }
            NSLog(@"\n-------------------------\n");
        }
        sqlite3_finalize(stmt);
        sqlite3_close(m_sqliteDB); 
}




@end
